#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2014-08-22 01:14:23
# @Author  : shitao.tommy (hero007asd@gmail.com)
# @Link    : http://example.org
# @Version : $Id$


from framework.db.mysql import BaseDb
from tornado import gen
from lib.libmd5 import md5
from lib import log

class Room(BaseDb):

    @gen.coroutine
    def new(self, room_name, dorm_id, school_id, remark):
        resp = ''
        ins_sql = "INSERT INTO tb_room(Fname, Fdorm_id, Fdorm_name, Fschool_id, Fremark, Fcreate_time) SELECT %s, %s, Fdorm_name, %s, %s, UNIX_TIMESTAMP() FROM tb_dorm WHERE Fid=%s"
        try:
            sel_cur = yield self.upsert_commit(ins_sql, (room_name, dorm_id, school_id, remark, dorm_id))
            resp = sel_cur
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
        raise gen.Return(resp)


    @gen.coroutine
    def list(self, school_id, start, offset):
        resp = []
        sel_sql = "SELECT Fid, Fname,Fdorm_name,Fremark,Fcreate_time FROM tb_room WHERE Fschool_id=%s ORDER BY Fid DESC LIMIT %s,%s"
        cnt_sql = "SELECT COUNT(Fid) FROM tb_room WHERE Fschool_id=%s"
        sel_dorm_sql = "SELECT Fid, Fdorm_name FROM tb_dorm WHERE Fschool_id=%s"

        try:
            sel_cur = yield self.executejson(sel_sql, (school_id,start,offset))
            cnt_ret = yield self.execute(cnt_sql, (school_id,))
            sel_dorm_cur = yield self.executejson(sel_dorm_sql, (school_id,))
            resp = [cnt_ret[0][0], sel_cur, sel_dorm_cur]
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
        raise gen.Return(resp)

    @gen.coroutine
    def get_dorm_list(self, school_id):
        resp = []
        sel_dorm_sql = "SELECT Fid, Fdorm_name FROM tb_dorm WHERE Fschool_id=%s"

        try:
            sel_dorm_cur = yield self.executejson(sel_dorm_sql, (school_id,))
            resp = sel_dorm_cur
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
        raise gen.Return(resp)

    @gen.coroutine
    def edit_info(self, school_id, room_id):
        resp = []
        sel_sql = "SELECT Fid,Fname, Fdorm_id, Fschool_id, Fremark FROM tb_room WHERE Fid=%s AND Fschool_id=%s"
        sel_dorm_sql = "SELECT Fid, Fdorm_name FROM tb_dorm WHERE Fschool_id=%s"
        try:
            sel_cur = yield self.executejson(sel_sql, (room_id,school_id))
            sel_dorm_cur = yield self.executejson(sel_dorm_sql, (school_id))
            resp = [sel_cur[0], sel_dorm_cur]
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)

        raise gen.Return(resp)

    @gen.coroutine
    def edit(self, room_id, room_name, dorm_id, school_id, remark):
        resp = 0
        upd_sql = "UPDATE tb_room a INNER JOIN tb_dorm b ON b.Fid = %s \
                    SET a.Fdorm_name=b.Fdorm_name, a.Fname=%s, a.Fdorm_id=%s, a.Fschool_id=%s, a.Fremark=%s WHERE a.Fid=%s"

        try:
            sel_cur = yield self.upsert_commit(upd_sql, (dorm_id,room_name,dorm_id,school_id,remark,room_id))
            resp = sel_cur
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
            resp = 0

        raise gen.Return(resp)


    @gen.coroutine
    def delete(self, room_id, school_id):
        resp = 0
        del_sql = "DELETE FROM tb_room WHERE Fid=%s AND Fschool_id=%s"

        try:
            del_ret = yield self.upsert_commit(del_sql, (room_id,school_id))
            resp = del_ret
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
            resp = 0

        raise gen.Return(resp)


    @gen.coroutine
    def search(self, room_name, dorm_id, school_id, start, offset):
        resp = []
        sel_sql = []
        cnt_sql = []
        sel_list = []
        cnt_list = []

        sel_sql.append("SELECT Fid, Fname,Fdorm_name,Fdorm_id,Fremark,Fcreate_time FROM tb_room WHERE Fschool_id=%s ")
        cnt_sql.append("SELECT COUNT(Fid) FROM tb_room WHERE Fschool_id=%s")
        sel_dorm_sql = "SELECT Fid, Fdorm_name FROM tb_dorm WHERE Fschool_id=%s"
        sel_list.append(school_id)
        cnt_list.append(school_id)
        if room_name != '':
            sel_sql.append(" AND Fname=%s")
            cnt_sql.append(" AND Fname=%s")
            sel_list.append(room_name)
            cnt_list.append(room_name)
        if dorm_id != '':
            sel_sql.append(" AND Fdorm_id=%s")
            cnt_sql.append(" AND Fdorm_id=%s")
            sel_list.append(dorm_id)
            cnt_list.append(dorm_id)
        sel_sql.append(" ORDER BY Fid DESC LIMIT %s,%s")
        sel_list.append(start)
        sel_list.append(offset)
        sel_sql = " ".join(sel_sql)
        cnt_sql = " ".join(cnt_sql)
        sel_tpl = tuple(sel_list)
        cnt_tpl = tuple(cnt_list)
        try:
            sel_cur = yield self.executejson(sel_sql, sel_tpl)
            cnt_ret = yield self.execute(cnt_sql, cnt_tpl)
            sel_dorm_cur = yield self.executejson(sel_dorm_sql, (school_id,))
            resp = [cnt_ret[0][0], sel_cur, sel_dorm_cur]
        except Exception, e:
            log.log_exception(self.__class__.__name__, e)
        raise gen.Return(resp)


